﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;

using MySql.Data.MySqlClient;

namespace hxlxc
{
    class MySqlConn
    {
        private static string ConnctionStr =
            "server=127.0.0.1;port=3306;user=root;password=root; database=omdb;Charset=utf8";

        public static int GetMaxBatchId()
        {
            // server=127.0.0.1/localhost 代表本机，端口号port默认是3306可以不写
            MySqlConnection conn = new MySqlConnection(ConnctionStr);
            int maxBatchId = 0;
            try
            {
                conn.Open();//打开通道，建立连接，可能出现异常,使用try catch语句
                string sqlSelect = "SELECT IFNULL(MAX(batch_id),0) as max_batch_id from excel_import_tbl_02";
                MySqlCommand sqlCmd = new MySqlCommand(sqlSelect, conn);
                MySqlDataAdapter sda = new MySqlDataAdapter(sqlCmd);
                DataSet ds = new DataSet();
                sda.Fill(ds);

                DataTable dt = ds.Tables[0];
                maxBatchId = int.Parse(dt.Rows[0][0].ToString());
            }
            catch (MySqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return maxBatchId;
        }

        public static void BatchInsert(List<string> list)
        {
            // server=127.0.0.1/localhost 代表本机，端口号port默认是3306可以不写
            MySqlConnection conn = new MySqlConnection(ConnctionStr);
            try
            {
                conn.Open();//打开通道，建立连接，可能出现异常,使用try catch语句

                Console.WriteLine("已经建立连接");
                //在这里使用代码对数据库进行增删查改
                MySqlCommand mySqlCommand;
                String sqlInsert;
                MySqlTransaction transaction = conn.BeginTransaction();

                if (list != null)
                {
                    try
                    {
                        for (int i = 0; i < list.Count; i++)
                        {
                            sqlInsert = list[i];
                            mySqlCommand = new MySqlCommand(sqlInsert, conn);
                            mySqlCommand.Transaction = transaction;
                            mySqlCommand.ExecuteNonQuery();
                        }
                        transaction.Commit(); // 如果 list.Count 过大，需要分段提交
                    }
                    catch (Exception e1)
                    {
                        transaction.Rollback();
                        throw e1;
                    }
                }

                Console.WriteLine("---插入完成---");
            }
            catch (MySqlException ex)
            {
                Console.WriteLine(ex.Message);
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        public static DataSet GetTotal_Number_Orders(String v_sql)
        {
            // server=127.0.0.1/localhost 代表本机，端口号port默认是3306可以不写
            MySqlConnection conn = new MySqlConnection(ConnctionStr);
            DataSet dataset = new DataSet();
            try
            {
                conn.Open();//打开通道，建立连接，可能出现异常,使用try catch语句
                MySqlDataAdapter adapter = new MySqlDataAdapter();
                adapter.SelectCommand = new MySqlCommand(v_sql, conn);
                adapter.Fill(dataset);

                //MySqlDataAdapter adapter = new MySqlDataAdapter();
                //String query = "select `订单编号` from excel_order_composite_tbl";
                //adapter.SelectCommand = new MySqlCommand(query, conn);
                //adapter.Fill(dataset);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return dataset;
        }

        // 通用查询
        public static DataSet Common_Query(String v_sql)
        {
            // server=127.0.0.1/localhost 代表本机，端口号port默认是3306可以不写
            MySqlConnection conn = new MySqlConnection(ConnctionStr);
            DataSet dataset = new DataSet();
            try
            {
                conn.Open();//打开通道，建立连接，可能出现异常,使用try catch语句
                MySqlDataAdapter adapter = new MySqlDataAdapter();
                // string test_sql = "SELECT a.`商品编码`, COUNT(a.`商品编码`) as _count from excel_import_tbl_02 a where a.createa_date = str_to_date('2018-10-09', '%Y-%m-%d') GROUP BY a.`商品编码` ORDER BY a.`商品编码`";
                adapter.SelectCommand = new MySqlCommand(v_sql, conn);
                adapter.Fill(dataset);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return dataset;
        }

    }
}
